﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace DLL
{
    public class DA_HD_BH_TSKT
    {

        // Chuỗi kết nối
        string strConnection;

        // SqlConnection
        SqlConnection sqlConn = null;
        private StringBuilder sbCommand = new StringBuilder(String.Empty);

        // Ten bang
        private const string c_table = "HD_BH_TSKT";

        // Ten cot cua bang Hop Dong
        private const string c_hop_dong_id = "hop_dong_id";
        //private const string c_hop_dong_name = "hop_dong_name";
        private const string c_ma_dvi = "ma_dvi";
        private const string c_investor_id = "investor_id";
        private const string c_package_id = "package_id";
        private const string c_phiBH = "phiBH";
        private const string c_signing_date = "signing_date";

        public DA_HD_BH_TSKT(string strConnection)
        {
            this.strConnection = strConnection;
        }

        private void OpenConnection()
        {
            sqlConn = new SqlConnection(strConnection);
            sqlConn.Open();
        }

        private void CloseConnection()
        {
            sqlConn.Close();
        }

        public DataSet Gethop_dongInfo(int investor_id)
        {
            DataSet ds = new DataSet();
            this.OpenConnection();
            sbCommand = new StringBuilder(String.Empty);
            sbCommand.AppendLine("SELECT  hop_dong_id");
            //sbCommand.AppendLine(",hop_dong_name");
            sbCommand.AppendLine(",investor_name");
            sbCommand.AppendLine(",package_name");
            sbCommand.AppendLine(",total_investment");
            sbCommand.AppendLine(",phiBH");
            sbCommand.AppendLine(",signing_date");
            sbCommand.AppendFormat("FROM {0} CT", c_table).AppendLine();
            sbCommand.AppendLine("INNER JOIN investor IVT");
            sbCommand.AppendLine("ON CT.investor_id = IVT.investor_id");
            sbCommand.AppendLine("LEFT OUTER JOIN pack PK");
            sbCommand.AppendLine("ON CT.package_id = PK.package_id");

            if (investor_id > 0)
            {
                sbCommand.AppendFormat("WHERE CT.investor_id = N'{0}'", investor_id).AppendLine();
            }

            SqlCommand cmd = new SqlCommand(sbCommand.ToString(), sqlConn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            this.CloseConnection();
            return ds;
        }

        private bool IsExisthop_dong(BLL.BO_HD_BH_TSKT hop_dong, string strConnection)
        {
            sbCommand = new StringBuilder(String.Empty);
            string hop_dong_id = hop_dong.Hop_dong_id.ToString();
            sbCommand.AppendFormat("SELECT * FROM {0} WHERE {1} = @hop_dong_id", c_table, c_hop_dong_id).AppendLine();
            SqlConnection sqlConnection = new SqlConnection(strConnection);
            SqlDataAdapter da = new SqlDataAdapter(sbCommand.ToString(), sqlConnection);
            da.SelectCommand.Parameters.AddWithValue("@hop_dong_id", hop_dong_id);
            DataTable dataTable = new DataTable();
            da.Fill(dataTable);

            if (dataTable.Rows.Count < 1)
            {
                // Hop dong chua ton tai
                return false;
            }
            else
            {
                // Hop Dong da ton tai
                return true;
            }
        }


        private string BuildInsertString(BLL.BO_HD_BH_TSKT hop_dong)
        {
            sbCommand = new StringBuilder(String.Empty);
            sbCommand.AppendFormat("INSERT INTO {0}", c_table).AppendLine();
            //sbCommand.AppendFormat("({0},", c_hop_dong_name).AppendLine();
            sbCommand.AppendFormat("({0},", c_ma_dvi).AppendLine();
            sbCommand.AppendFormat("{0},", c_investor_id).AppendLine();
            sbCommand.AppendFormat("{0},", c_package_id).AppendLine();
            sbCommand.AppendFormat("{0},", c_phiBH).AppendLine();
            sbCommand.AppendFormat("{0})", c_signing_date).AppendLine();
            sbCommand.AppendFormat("VALUES(N'{0}', N'{1}', N'{2}', N'{3}', N'{4}')", hop_dong.Ma_dvi, hop_dong.Investor_id, hop_dong.Package_id, hop_dong.PhiBH, hop_dong.Signing_date).AppendLine();

            return sbCommand.ToString();
        }

        public bool Createhop_dong(BLL.BO_HD_BH_TSKT hop_dong, string strConnection)
        {

            string strINSERT = this.BuildInsertString(hop_dong);

            // Kiem tra chu dau tu da ton tai trong co so du lieu chua
            if (IsExisthop_dong(hop_dong, strConnection))
            {
                return false;
            }
            else
            {
                SqlConnection sqlConnection = new SqlConnection(strConnection);
                sqlConnection.Open();
                SqlCommand sqlCommand = new SqlCommand(strINSERT, sqlConnection);

                // execute Query
                sqlCommand.ExecuteNonQuery();
                sqlConnection.Close();
                return true;
            }
        }
    }
}
